---
title: "pk73102_policy stored procedure documentation"
summary: "Analysis of the Oracle package function that determines policy submission ownership, including business rules and operations."
status: draft
owner: SAIF Documentation Generator
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "pk73102_policy"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk73102_policy"
retrieval_date: "2026-08-31"
---

# pk73102_policy

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | Oracle (resolved via the caller's `IDataPortalInfo`; not named in code) |
| **Calling libraries** | `SAIF.Business.Policy` |
| **Calling classes** | `PolicyInformation` |

## Purpose

Package `pk73102_policy` exposes submission-status and ownership operations for policies, including `reject_submission`, `unreject_submission`, `set_submission_to_pending`, `set_submission_to_quoted`, `set_app_received_date`, `is_pms_status`, `is_pms_status_yn`, and `insert_dividend_notepad`. Only one member, the `does_wcis_own_submission` **function** (not a procedure), is invoked by `SAIF.Business.Policy` — it determines whether WCIS owns a policy's submission based on the policy's current submission-status code.

## Procedures / Methods

### does_wcis_own_submission

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` — package **function**, not a procedure |
| **Called by** | `PolicyInformation.GetPolicyOwnershipSystem` — `SAIF.Business.Policy/PolicyInformation.vb:249–274` |

**Description:** Returns the Y/N `wcis_owns_flag` for the policy's current submission status, joining `submission_status_code` to `sub_policy` on `policy_num`. Defaults to `'Y'` (WCIS-owned) when no matching row is found or on any other error. The BOF call site is intentionally a `CommandType.Text` scalar `SELECT … FROM dual` wrapper (`PolicyInformation.vb:258`) — the commented-out `StoredProcedure` form is retained on line 257 as historical context only.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` (anchored numeric) | — | Policy number whose submission ownership is being resolved. Bound by the .NET caller from `inPolicyNumber As Decimal` (`PolicyInformation.vb:249`, bound at line 261). |
| *(return value)* | RETURN | `VARCHAR2` (1 character; body assigns into local `v_wcis_owns_ind VARCHAR2(1)` sourced from `submission_status_code.wcis_owns_flag`) | — | Y/N flag indicating whether WCIS owns the submission. Consumed by the caller as `PortalRequest.DataRequest(0).Result.ToString.ToBoolean` (`PolicyInformation.vb:268`). |

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BP-006 | WCIS ownership of a policy submission is determined by the `wcis_owns_flag` on the submission-status code currently assigned to the policy's sub-policy record. | Always evaluated for the supplied `in_policy_num` | `SELECT ssc.wcis_owns_flag` from `submission_status_code ssc` joined to `sub_policy s` on `s.submission_status_code = ssc.code` where `s.policy_num = in_policy_num`; return that flag. |
| PR-BP-007 | When ownership cannot be determined — no matching sub-policy/status row, or any other error — the policy defaults to being owned by WCIS. | `WHEN NO_DATA_FOUND` **or** `WHEN OTHERS` | `RETURN 'Y'` in both handlers. This fail-open default means a missing `sub_policy` row is indistinguishable from a genuine WCIS-owned policy, and the .NET caller consequently returns `Enums.PolicyOwnershipSystems.WCIS` (`PolicyInformation.vb:268–272`). |

> These rules are documented in [business-rules.md](../business-rules.md). Newly introduced rules must be added to `business-rules.md` as part of the same writing task.

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| Determine submission ownership system | Returns the Y/N `wcis_owns_flag` for the policy's current submission status, defaulting to `'Y'` on any failure. | `PolicyInformation.GetPolicyOwnershipSystem` invoked with a policy number (documented as OP-11); executed as a scalar `SELECT … FROM dual`. |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
   RETURN VARCHAR2;
```

</details>

<details>
<summary>PL/SQL package body</summary>

```sql
FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
      RETURN VARCHAR2
   IS
      v_wcis_owns_ind   VARCHAR2(1);
   BEGIN
      SELECT wcis_owns_flag
        INTO v_wcis_owns_ind
        FROM submission_status_code ssc
           , sub_policy s
       WHERE s.submission_status_code = ssc.code
             AND s.policy_num = in_policy_num;

      RETURN v_wcis_owns_ind;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN 'Y';
      WHEN OTHERS THEN
         RETURN 'Y';
   END does_wcis_own_submission;
```

</details>

.NET invocation (as executed by BOF):

```sql
SELECT pk73102_policy.does_wcis_own_submission(:in_policy_num)
  FROM dual
```

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: WCIS_DBA.pk73102_policy
- Legends CLI query executed 2026-08-31: `legends oracle query --name pk73102_policy`
- Calling code: `SAIF.Business.Policy/PolicyInformation.vb:249–274`